package java.com.fitness.club.util;

import com.fitness.club.model.ColumnInfo;
import com.fitness.club.model.DatabaseConfig;
import org.springframework.util.ClassUtils;

import java.com.fitness.club.model.TableInfo;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DatabaseUtil {

    private final static String MsSqlSchema = "DBO";

    private final static String configFilePath = "config/database-mysql.properties";
//	private final static String configFilePath = "config/database-mssql.properties";


    /**
     * 数据库的Connection
     */
    private static Connection conn;

    /**
     * 数据库属性的DatabaseMetaData
     */
    private static DatabaseMetaData dbmd;

    public static DatabaseConfig databaseConfig;

    static {
        try {
            PropertiesUtil putil = new PropertiesUtil(configFilePath);
            String dbType = putil.getProperty("database.type").toLowerCase();
            String driverClassName = putil.getProperty("jdbc.driverClassName");
            String url = putil.getProperty("jdbc.url");
            String userName = putil.getProperty("jdbc.username");
            String password = putil.getProperty("jdbc.password");
            String dbName = putil.getProperty("database.name");

            String savePath = putil.getProperty("file.savepath");

            String basePackage = putil.getProperty("base.package");

            String[] tableNameArray = null;

            String tableNames = putil.getProperty("create.table.names");
            if (!tableNames.equals("") && !tableNames.equals("*")) {
                tableNameArray = tableNames.split(";");
            }

            String[] fieldPrefixArray = null;
            String fieldPrefixs = putil.getProperty("field.prefix");
            if (!fieldPrefixs.equals("")) {
                fieldPrefixArray = fieldPrefixs.split(";");
            }

            String exclusionTables = putil.getProperty("exclusion.tables");

            databaseConfig = new DatabaseConfig(dbType, driverClassName, url, userName, password, dbName, basePackage,
                    savePath, tableNameArray, fieldPrefixArray, exclusionTables);
            // 初始化Connection
            Class.forName(driverClassName);

            conn = DriverManager.getConnection(url, userName, password);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

    }

    public static List<TableInfo> getTableInfo(String[] tables) throws SQLException {
        List<TableInfo> tableInfos = new ArrayList<TableInfo>();
        dbmd = conn.getMetaData();
        ResultSet rs = null;
        if (tables != null && tables.length > 0) {
            for (String table : tables) {
                if (databaseConfig.getType().equals("mysql")) {
                    rs = dbmd.getTables("", databaseConfig.getDbName(), table, new String[]{"TABLE", "VIEW"});
                } else if (databaseConfig.getType().equals("mssql")) {
                    rs = dbmd.getTables(databaseConfig.getDbName(), MsSqlSchema, table, new String[]{"TABLE", "VIEW"});
                }
                DatabaseUtil.addTableToList(tableInfos, rs);
            }
        } else {
            if (databaseConfig.getType().equals("mysql")) {
                rs = dbmd.getTables("", databaseConfig.getDbName(), "", new String[]{"TABLE", "VIEW"});
            } else if (databaseConfig.getType().equals("mssql")) {
                rs = dbmd.getTables(databaseConfig.getDbName(), MsSqlSchema, null, new String[]{"TABLE", "VIEW"});
            }
            DatabaseUtil.addTableToList(tableInfos, rs);
        }
        for (TableInfo table : tableInfos) {
            System.out.println("正在获取表【" + table.getDbTableName() + "】信息");
            List<ColumnInfo> columnInfos = DatabaseUtil.getTableColumnInfos(table.getDbTableName());
            table.setColumns(columnInfos);
        }
        conn.close();
        return tableInfos;
    }

    // 获取表格的列信息
    private static List<ColumnInfo> getTableColumnInfos(String tablename) throws SQLException {
        ResultSet rs = null;
        if (databaseConfig.getType().equals("mysql")) {
            rs = dbmd.getColumns("", databaseConfig.getDbName(), tablename, "");
        } else if (databaseConfig.getType().equals("mssql")) {
            rs = dbmd.getColumns(databaseConfig.getDbName(), MsSqlSchema, tablename, null);
        }
        List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
        while (rs.next()) {
            String dbColumnName = rs.getString("COLUMN_NAME").trim();
            String dbColumnType = rs.getString("TYPE_NAME").trim();
            String remarks = rs.getString("REMARKS");
            String columnSize = rs.getString("COLUMN_SIZE");

            ColumnInfo info = new ColumnInfo();
            info.setDbColumnName(dbColumnName);
            info.setDbColumnType(dbColumnType);
            info.setColumnName(com.fitness.club.util.DataStringUtil.getEntityFileName(databaseConfig.getFieldPrefix(), dbColumnName));
            info.setRemarks(remarks);
            info.setColumnSize(columnSize);
            info.setColumnType(com.fitness.club.util.DataStringUtil.getColumnType(dbColumnType));
            columns.add(info);
        }

        if (rs.isClosed()) {
            rs.close();
        }
        return columns;
    }

    // 添加信息到表对象实体类中
    private static void addTableToList(List<TableInfo> tableInfos, ResultSet rs) throws SQLException {
        while (rs.next()) {
            String dbTableName = rs.getString("TABLE_NAME").trim();
            if (databaseConfig.getExclusionTables().contains(dbTableName)) {
                continue;
            }
            String tableType = rs.getString("TABLE_TYPE");
            String idField = DatabaseUtil.getPkField(dbTableName).trim();

            TableInfo table = new TableInfo();
            table.setDbTableName(dbTableName);
            table.setTableType(tableType.toLowerCase());
            table.setTableName(com.fitness.club.util.DataStringUtil.getEntityFileName(databaseConfig.getFieldPrefix(), dbTableName));
            if (databaseConfig.getType().equals("mysql")) {
                table.setTableComment(DatabaseUtil.getTableComment(dbTableName));
            } else if (databaseConfig.getType().equals("mssql")) {
                table.setTableComment("");

                //start
//				table.setTableComment(DatabaseUtil.getSQlTableComment(dbTableName));
                //end
            }
            table.setDbIdField(idField);
            table.setIdField(com.fitness.club.util.DataStringUtil.getEntityFileName(databaseConfig.getFieldPrefix(), idField));
            tableInfos.add(table);
        }
        if (rs.isClosed()) {
            rs.close();
        }
    }

    /**
     * 获取表的主键
     *
     * @param dbTableName
     * @return
     * @throws SQLException
     */
    private static String getPkField(String dbTableName) throws SQLException {
        ResultSet rs = null;
        if (databaseConfig.getType().equalsIgnoreCase("mysql")) {
            rs = dbmd.getPrimaryKeys("", databaseConfig.getDbName(), dbTableName);
        } else {
            rs = dbmd.getPrimaryKeys(databaseConfig.getDbName(), MsSqlSchema, dbTableName);
        }
        String idField = "";
        if (rs != null && rs.next()) {
            idField = rs.getString("COLUMN_NAME");
        }
        if (rs.isClosed()) {
            rs.close();
        }
        return idField;
    }

    /**
     * 获取表的注释
     *
     * @param table
     * @return
     * @throws SQLException
     */
    private static String getTableComment(String table) throws SQLException {
        Statement stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table);
        String comment = "";
        if (rs != null && rs.next()) {
            String createDDL = rs.getString(2);
            comment = parse(createDDL);
        }
        if (rs.isClosed()) {
            rs.close();
        }
        return comment;
    }

    /**
     * 获取SQLServer表的注释
     *
     * @param table
     * @return
     * @throws SQLException
     */
    private static String getSQlTableComment(String table) throws SQLException {
        ResultSet rs = null;
        String comment = "";
        if (databaseConfig.getType().equalsIgnoreCase("mysql")) {
            Statement stmt = (Statement) conn.createStatement();
            rs = stmt.executeQuery("SHOW CREATE TABLE " + table);
            if (rs != null && rs.next()) {
                String createDDL = rs.getString(2);
                comment = parse(createDDL);
            }
        } else {
            String sql = "Select * from (SELECT DISTINCT d.name,f.value FROM syscolumns a LEFT JOIN systypes b ON a.xusertype= b.xusertype INNER JOIN sysobjects d ON a.id= d.id  AND d.xtype= 'U' AND d.name<> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault= e.id LEFT JOIN sys.extended_properties g ON a.id= G.major_id  AND a.colid= g.minor_id LEFT JOIN sys.extended_properties f ON d.id= f.major_id  AND f.minor_id= 0) as asb where asb.name = '";
            sql += table + "'";
            PreparedStatement stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            if (rs != null && rs.next()) {
                String createDDL = rs.getString(2);
                comment = createDDL;
            }
        }

        if (rs.isClosed()) {
            rs.close();
        }
        return comment;
    }

    private static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

    public static void main(String[] args) {
        String path1 = System.getProperty("user.dir");
        String path = ClassUtils.getDefaultClassLoader().getResource("").getPath();
        System.err.println(path);
        System.err.println(path1);
    }
}
